import logging
import os

import pymysql

from student_test.config import  config
import sqlite3


class DButils(object):
    # 初始化
    __conn = None
    __cursour = None
    # __url = "D:/chicaixiang/auto-test/Interface test/student/student_env/studentManagementSystem/db.sqlite3"
    # # __url = "/home/ubuntu/studnet_test/student_env/studentManagementSystem/db.sqlite3"
    # # __url = "{0}\\studentManagementSystem\\db.sqlite3".format(PROJECT_DIR)


    # 创建连接
    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            # cls.__conn = sqlite3.connect(cls.__url)# sqlite3
            cls.__conn = pymysql.connect(**config)
        return cls.__conn
    # 获取游标
    @classmethod
    def __get_cursor(cls):
        if cls.__cursour is None:
            cls.__cursour = cls.__get_conn().cursor()
        return cls.__cursour
    # 执行sql
    @classmethod
    def exec_sql(cls,sql):
        try:
            # 获取游标对象
            cursor = cls.__get_cursor()
            # 调用游标对象的execute方法，执行sql
            cursor.execute(sql)
            # sql = "select * from xxx"   # sql = "update   delete  insert"
            #  如果是查询
            if sql.split()[0].lower() == "select":# sql语句中的第一个元素是不是select
                # 返回所有数据
                return cursor.fetchall()
            #  否则：
            else:
                # 提交事务
                cls.__conn.commit()
                # 返回受影响的行数
                return cursor.rowcount # 增删改返回的的影响行数rowcount
        except Exception as e:
            cls.__conn.rollback()
            #打印异常信息
            print(e)
            logging.info("数据库异常:{}".format(e))
        finally:
         # 关闭游标
             cls.__close_cursor()
              # 关闭连接
             cls.__close_conn()
    # 关闭游标
    @classmethod
    def __close_cursor(cls):
        if cls.__cursour:
            cls.__cursour.close()
            cls.__cursour= None
     # 关闭连接
    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None


# 查询数据中数量




def init_db():
    """
    初始化数据库，删除掉departments的所有数据
    :return:
    """
    sql = "delete from departments "
    db=DButils()
    db.exec_sql(sql)

    # print(execute_db(sql))
# 查询list_data的数据
def count_db():
    sql = "SELECT COUNT(*) from departments"
    db = DButils()
    count = db.exec_sql(sql)[0][0]
    return count
# 插入测试数据
def insert_db():
    init_db()
    sql ="INSERT INTO departments (dep_id,dep_name,master_name,slogan) VALUES" \
         "('T2001N','Test201学院','Master201','Slogan201')," \
         "('T2002N','Test202学院','Master202','')," \
         "('T2003N','Test203学院','Master203','')," \
         "('T2004N','Test204学院','Master204','')," \
         "('T2005N','Test205学院','Master205','Slogan205')," \
         "('T2006N','Test206学院','Master206','Slogan206')," \
         "('T2007N','Test207学院','Master207','Slogan207')," \
         "('T2008N','Test208学院','Master208','Slogan208')," \
         " ('T2009N','Test209学院','Master291','Slogan209')," \
         "('T2010N','Test210学院','Master210','Slogan210')"

    db = DButils()
    db.exec_sql(sql)
if __name__ == '__main__':
    # print(PROJECT_DIR+os.sep+"studentManagementSystem"+os.sep+"db.sqlite3")
    #
    init_db()
    # query_db("dep_id","T02","T03")
    print(count_db())
    insert_db()
    sql ="select * from departments"
    db=DButils()
    print(db.exec_sql(sql))
    # url = PROJECT_DIR + os.sep + "studentManagementSystem" + os.sep + "db.sqlite3"
    # url1 = "{0}\\studentManagementSystem\\db.sqlite3".format(PROJECT_DIR)
    # print(url)
    # print(123)
    # print(url1)
    #
    # URL = "D:\chicaixiang\auto-test\Interface test\student\student_env\studentManagementSystem\db.sqlite3"
    # print(PROJECT_DIR)
    # print(URL)